from backend.utils.db import get_db_connection
from datetime import datetime


class ChatService:
    def save_message(self, user_id, message, sender_type):
        # 限制 sender_type 只能是合法值
        valid_types = ["user", "admin", "assistant"]
        if sender_type not in valid_types:
            sender_type = "user"  # 默认值
        """保存消息记录"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      INSERT INTO chat_history
                          (user_id, message, sender_type, created_at)
                      VALUES (%s, %s, %s, %s) 
                      """
                created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cursor.execute(sql, (
                    user_id,
                    message,
                    sender_type,
                    created_at
                ))
                conn.commit()
                return True
        except Exception as e:
            conn.rollback()
            print(f"保存消息失败: {e}")
            return False

    def get_chat_history(self, user_id):
        """获取用户的所有聊天历史记录"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT id, user_id, message, sender_type, created_at
                      FROM chat_history
                      WHERE user_id = %s
                      ORDER BY created_at ASC 
                      """
                cursor.execute(sql, (user_id,))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取聊天历史失败: {e}")
            return []

    def get_chat_history_by_sender(self, user_id, sender_type):
        """根据发送者类型获取用户聊天历史记录"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT id, user_id, message, sender_type, created_at
                      FROM chat_history
                      WHERE user_id = %s 
                        AND sender_type = %s
                      ORDER BY created_at ASC 
                      """
                cursor.execute(sql, (user_id, sender_type))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取聊天历史失败: {e}")
            return []

    def create_human_chat_request(self, user_id):
        """创建人工客服请求"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      INSERT INTO human_chat_requests
                          (user_id, status, created_at)
                      VALUES (%s, %s, %s) 
                      """
                created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cursor.execute(sql, (user_id, 'pending', created_at))
                conn.commit()
                return cursor.lastrowid
        except Exception as e:
            conn.rollback()
            print(f"创建人工客服请求失败: {e}")
            return None

    def get_pending_human_chat_requests(self):
        """获取待处理的人工客服请求"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT request_id, user_id, created_at
                      FROM human_chat_requests
                      WHERE status = 'pending'
                      ORDER BY created_at ASC 
                      """
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(f"获取待处理人工请求失败: {e}")
            return []

    def accept_human_chat_request(self, request_id, admin_id):
        """接受人工客服请求"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      UPDATE human_chat_requests
                      SET status      = 'accepted', 
                          admin_id    = %s, 
                          accepted_at = %s
                      WHERE request_id = %s 
                      """
                accepted_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cursor.execute(sql, (admin_id, accepted_at, request_id))
                conn.commit()

                # 获取对应的用户ID
                cursor.execute("SELECT user_id FROM human_chat_requests WHERE request_id = %s", (request_id,))
                result = cursor.fetchone()
                return result['user_id'] if result else None
        except Exception as e:
            conn.rollback()
            print(f"接受人工客服请求失败: {e}")
            return None